import pandas as pd
from datetime import datetime

def parse_excel_and_merge_logic(file_path):
    """
    Parses the specified Excel file and merges logic from '通用', '产品', '区域', and '项目' sheets.
    The hierarchy is: 项目 > 区域 > 产品 > 通用.
    The file handle is properly closed after reading.
    Returns a dictionary where each key is an indicator name and the value contains
    the logic details with the correct priority applied, including timestamps.
    """
    data = {}
    try:
        with pd.ExcelFile(file_path) as xls:
            # Helper to process each sheet
            def process_sheet(sheet_name, id_columns=None):
                if sheet_name in xls.sheet_names:
                    df = pd.read_excel(xls, sheet_name).dropna(subset=['指标名称', '计算逻辑'])
                    for _, row in df.iterrows():
                        indicator = row['指标名称']
                        logic = row['计算逻辑']

                        
                        # 解析时间戳字段
                        created_at = None
                        updated_at = None
                        
                        if '创建日期' in row and pd.notna(row['创建日期']):
                            try:
                                if isinstance(row['创建日期'], str):
                                    created_at = datetime.strptime(row['创建日期'], '%Y-%m-%d %H:%M:%S').isoformat()
                                else:
                                    created_at = row['创建日期'].isoformat() if hasattr(row['创建日期'], 'isoformat') else str(row['创建日期'])
                            except:
                                created_at = str(row['创建日期'])
                        
                        if '更新日期' in row and pd.notna(row['更新日期']):
                            try:
                                if isinstance(row['更新日期'], str):
                                    updated_at = datetime.strptime(row['更新日期'], '%Y-%m-%d %H:%M:%S').isoformat()
                                else:
                                    updated_at = row['更新日期'].isoformat() if hasattr(row['更新日期'], 'isoformat') else str(row['更新日期'])
                            except:
                                updated_at = str(row['更新日期'])
                        
                        data.setdefault(indicator, {
                            'created_at': None,
                            'updated_at': None
                        })
                        
                        # 智能合并时间戳：取最新的时间戳
                        if created_at:
                            existing_created = data[indicator]['created_at']
                            if not existing_created or created_at > existing_created:
                                data[indicator]['created_at'] = created_at
                        
                        if updated_at:
                            existing_updated = data[indicator]['updated_at']
                            if not existing_updated or updated_at > existing_updated:
                                data[indicator]['updated_at'] = updated_at
                        
                        if id_columns:
                            # 处理复合主键的情况
                            if isinstance(id_columns, list):
                                # 区域sheet：需要区域编码和项目编码作为复合主键
                                if len(id_columns) == 2 and all(pd.notna(row[col]) for col in id_columns):
                                    region_code = row[id_columns[0]]  # 区域编码
                                    project_code = row[id_columns[1]]  # 项目编码
                                    composite_key = f"{region_code}_{project_code}"
                                    data[indicator].setdefault(sheet_name, {})[composite_key] = logic
                            else:
                                # 单一主键的情况（产品、项目）
                                if pd.notna(row[id_columns]):
                                    item_id = row[id_columns]
                                    data[indicator].setdefault(sheet_name, {})[item_id] = logic
                        else:
                            # 通用sheet，没有主键
                            data[indicator][sheet_name] = logic

            # Process sheets in reverse priority order (通用 -> 产品 -> 区域 -> 项目)
            # This way, we can just update the dictionary, and higher priority logic
            # will naturally be available alongside lower priority ones.
            process_sheet('通用')
            process_sheet('产品', '产品编码')
            process_sheet('区域', ['区域编码', '项目编码'])  # 区域使用复合主键
            process_sheet('项目', '项目编码')

    except FileNotFoundError:
        print(f"Error: The file was not found at {file_path}")
        return {}
    except Exception as e:
        print(f"An error occurred while parsing the Excel file: {e}")
        return {}
        
    return data